When multiple selections are made in a list box set to AND-mode, only data associated to all of the selected field values will be shown in the other fields.
Example:
Your data source contains information about which customers have purchased different articles.
When you select a couple of articles in the default mode, QlikView will show you the customers that have purchased any of the selected articles.
When you select a couple of articles in AND-mode, QlikView will show you the customers that have purchased all of the selected articles.
AND-Mode Prerequisites
The mode for the field is set in List Box Properties: General tab.
When And mode is activated, an ampersand ("&") will be displayed in front of selected values.
If clicking and holding a value for a moment, the selection will switch from (AND) selected (green) to NOT selected (red). The ampersand will also be replaced by an exclamation mark ("!"). The NOT selection which is a forced exclusion of the value or values so marked, can only be made when a list box is in And mode.
AND-Mode criteria
A field cannot always be set to logical AND-mode. The reason for this is that the and alternative is logically meaningful only if the concerned field is linked to only one other field. The following criteria must be fulfilled:
- the field must only exist in one logical table,
- the field must be the second column of no more than two columns,
- the table must not contain any duplicate records, and
- the field must be loaded with the distinct qualifier. If the table is loaded using a select statement, you must use a preceding load distinct *.
Setting a List Box in AND-Mode
The following procedure describes how to set a list box in AND-mode instead of the default mode (logical OR). Perform the following steps:
- Make sure that the AND-mode criteria are fulfilled.
- Right-click on the list box and select Properties.
- On the General tab, mark the And mode check box.
Forced Exclusion
Forced exclusion (sometimes referred to as NOT-selection) is closely related to the AND-selection. The forced exclusion makes it possible to explicitly exclude a field value, which means that the solutions found by QlikView may not be associated with the excluded value.
The forced exclusion is made by clicking a cell and keeping the mouse button pressed until the cell turns red. Ctrl-clicking in this manner is equivalent to an exclusion that is an additional requirement to the previous selections/ exclusions. The forced exclusion can only be made on a field that is in AND-mode.
If you want to force the exclusion of specific field values in objects such as text objects, you have to modify the script syntax. For example, if your script statement is the following:
=count({<ANDActor=>}DISTINCT Title)
Modify it as follows:
=count({<~ANDActor=, ANDActor=>} DISTINCT Title)
